﻿using Aspose.Cells;
using ProjectCloth.Entity;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ProjectCloth.FrmUI
{
    public partial class ImportSubject : Form
    {
        public ImportSubject()
        {
            InitializeComponent();
        }

        private void exportBtn_Click(object sender, EventArgs e)
        {
            label1.Text = "导入科目条数：0";
            //FileDialog fileDialog = new FileDialog();
            OpenFileDialog dialog = new OpenFileDialog();
            dialog.Multiselect = true;//该值确定是否可以选择多个文件
            dialog.Title = "请选择文件夹";
            dialog.Filter = "所有文件(*.*)|*.*";
            string file = "";
            if (dialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                file = dialog.FileName;
                var data = ExcelToTable(file);
                label1.Text = "导入科目条数：" + data.ToString();

            }
            MessageBox.Show("窗口将关闭！");
            this.Close();
        }
        public DataSet ExcelToDS(string Path)
        {
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            string strExcel = "";
            OleDbDataAdapter myCommand = null;
            DataSet ds = null;
            strExcel = "select no,name,batchprice,price from [sheet1$]";
            myCommand = new OleDbDataAdapter(strExcel, strConn);
            ds = new DataSet();
            myCommand.Fill(ds, "table1");
            return ds;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="admin"></param>
        /// <returns></returns>
        public int ExcelToTable(string filePath)
        {
            Workbook book = null;
            Worksheet sheet = null;
            book = new Workbook(filePath);
            sheet = book.Worksheets[0];
            var result = 0;
            int i = 1;
            int s = 0;
            int count = 0;
            List<string> stockAccountList = new List<string>();
            StringBuilder sb = new StringBuilder();
            var list = new List<Subject>();

            try
            {

                while (true)
                {
                    Cell celll = sheet.Cells[s, 0];
                    if (celll != null && !string.IsNullOrEmpty(celll.StringValue))
                    {
                        //1
                        string name = celll.StringValue;
                        var subject = new Subject()
                        {
                            ShortName= name,
                            Name= name,
                        };
                        list.Add(subject);
                        s++;
                    }
                    else
                    {
                        break;
                    }
                }
            }
            catch (Exception ex)
            {

            }
            finally
            {

            }
            var NewSubjectList = new List<Subject>();
            ClothContext db = new ClothContext();
            foreach (var subject in list)
            {
                if (db.Subjects.Where(o => o.Name == subject.Name).Count() == 0)
                {
                    NewSubjectList.Add(subject);
                }
            }
            db.Subjects.AddRange(NewSubjectList);
            result = db.SaveChanges();
            return result;
        }

        
    }
}